
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Comments_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Comments_Insert]

IF OBJECT_ID(N'[dbo].[Comments_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Comments_Update]

IF OBJECT_ID(N'[dbo].[Comments_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Comments_Delete]

IF OBJECT_ID(N'[dbo].[Comments_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Comments_Select]

--endregion

GO


--region [dbo].[Comments_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Comments_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdComment int = null,
	@CommentDetail nvarchar(max) = null,
	@IdParent int = null,
	@IdUser int = null,
	@IsApproved bit = null,
	@CreatedDate datetime = null,
	@LastModifiedDate datetime = null,
	@IdNews int = null,
	@IdMatch int = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Comments]
					WHERE
					(
						(@IdComment is null OR [Comments].[IdComment] = @IdComment)
						AND (@CommentDetail is null OR [Comments].[CommentDetail] like @CommentDetail)
						AND (@IdParent is null OR [Comments].[IdParent] = @IdParent)
						AND (@IdUser is null OR [Comments].[IdUser] = @IdUser)
						AND (@IsApproved is null OR [Comments].[IsApproved] = @IsApproved)
						AND (@CreatedDate is null OR [Comments].[CreatedDate] = @CreatedDate)
						AND (@LastModifiedDate is null OR [Comments].[LastModifiedDate] = @LastModifiedDate)
						AND (@IdNews is null OR [Comments].[IdNews] = @IdNews)
						AND (@IdMatch is null OR [Comments].[IdMatch] = @IdMatch)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Comments].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdComment' and @SortType = 1 THEN [Comments].[IdComment] END ASC,
						CASE WHEN @SortBy = 'IdComment' and @SortType = 0 THEN [Comments].[IdComment] END DESC,
						CASE WHEN @SortBy = 'CommentDetail' and @SortType = 1 THEN [Comments].[CommentDetail] END ASC,
						CASE WHEN @SortBy = 'CommentDetail' and @SortType = 0 THEN [Comments].[CommentDetail] END DESC,
						CASE WHEN @SortBy = 'IdParent' and @SortType = 1 THEN [Comments].[IdParent] END ASC,
						CASE WHEN @SortBy = 'IdParent' and @SortType = 0 THEN [Comments].[IdParent] END DESC,
						CASE WHEN @SortBy = 'IdUser' and @SortType = 1 THEN [Comments].[IdUser] END ASC,
						CASE WHEN @SortBy = 'IdUser' and @SortType = 0 THEN [Comments].[IdUser] END DESC,
						CASE WHEN @SortBy = 'IsApproved' and @SortType = 1 THEN [Comments].[IsApproved] END ASC,
						CASE WHEN @SortBy = 'IsApproved' and @SortType = 0 THEN [Comments].[IsApproved] END DESC,
						CASE WHEN @SortBy = 'CreatedDate' and @SortType = 1 THEN [Comments].[CreatedDate] END ASC,
						CASE WHEN @SortBy = 'CreatedDate' and @SortType = 0 THEN [Comments].[CreatedDate] END DESC,
						CASE WHEN @SortBy = 'LastModifiedDate' and @SortType = 1 THEN [Comments].[LastModifiedDate] END ASC,
						CASE WHEN @SortBy = 'LastModifiedDate' and @SortType = 0 THEN [Comments].[LastModifiedDate] END DESC,
						CASE WHEN @SortBy = 'IdNews' and @SortType = 1 THEN [Comments].[IdNews] END ASC,
						CASE WHEN @SortBy = 'IdNews' and @SortType = 0 THEN [Comments].[IdNews] END DESC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 1 THEN [Comments].[IdMatch] END ASC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 0 THEN [Comments].[IdMatch] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Comments]
			Where 
			(
				(@IdComment is null OR [Comments].[IdComment] = @IdComment)
				AND (@CommentDetail is null OR [Comments].[CommentDetail] like @CommentDetail)
				AND (@IdParent is null OR [Comments].[IdParent] = @IdParent)
				AND (@IdUser is null OR [Comments].[IdUser] = @IdUser)
				AND (@IsApproved is null OR [Comments].[IsApproved] = @IsApproved)
				AND (@CreatedDate is null OR [Comments].[CreatedDate] = @CreatedDate)
				AND (@LastModifiedDate is null OR [Comments].[LastModifiedDate] = @LastModifiedDate)
				AND (@IdNews is null OR [Comments].[IdNews] = @IdNews)
				AND (@IdMatch is null OR [Comments].[IdMatch] = @IdMatch)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Comments_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Comments_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Comments_Insert]
	@IdComment int OUTPUT,
	@CommentDetail nvarchar(max),
	@IdParent int,
	@IdUser int,
	@IsApproved bit,
	@CreatedDate datetime,
	@LastModifiedDate datetime,
	@IdNews int,
	@IdMatch int

AS


INSERT INTO [dbo].[Comments] 
(
	[CommentDetail],
	[IdParent],
	[IdUser],
	[IsApproved],
	[CreatedDate],
	[LastModifiedDate],
	[IdNews],
	[IdMatch]
)
VALUES 
(
	@CommentDetail,
	@IdParent,
	@IdUser,
	@IsApproved,
	@CreatedDate,
	@LastModifiedDate,
	@IdNews,
	@IdMatch
)

SET @IdComment = SCOPE_IDENTITY()

--end [dbo].[Comments_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Comments_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Comments_Update]
	@IdComment int,
	@CommentDetail nvarchar(max),
	@IdParent int,
	@IdUser int,
	@IsApproved bit,
	@CreatedDate datetime,
	@LastModifiedDate datetime,
	@IdNews int,
	@IdMatch int
AS


UPDATE [dbo].[Comments] SET
	[CommentDetail] = @CommentDetail,
	[IdParent] = @IdParent,
	[IdUser] = @IdUser,
	[IsApproved] = @IsApproved,
	[CreatedDate] = @CreatedDate,
	[LastModifiedDate] = @LastModifiedDate,
	[IdNews] = @IdNews,
	[IdMatch] = @IdMatch
WHERE
	[IdComment] = @IdComment

--end [dbo].[Comments_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Comments_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Comments_Delete]
	@IdComment int
AS


DELETE FROM [dbo].[Comments]
WHERE
(
	[IdComment] = @IdComment
)

--end [dbo].[Comments_Delete]
--endregion

GO
--=========================================================================================--

